Skip Headers

Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 13 Managing an Apply Process

A Streams apply process dequeues logical change records (LCRs) and user messages from a specific queue and either applies each one directly or passes it as a parameter to a user-defined procedure.

This chapter contains these topics:

- Creating an Apply Process

- Starting an Apply Process

- Stopping an Apply Process

- Managing the Rule Set for an Apply Process

- Setting an Apply Process Parameter

- Setting the Apply User for an Apply Process

- Managing the Message Handler for an Apply Process

- Managing the Precommit Handler for an Apply Process

- Specifying Message Enqueues by Apply Processes

- Specifying Execute Directives for Apply Processes

- Managing an Error Handler

- Managing Apply Errors

- Dropping an Apply Process

Each task described in this chapter should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.

See Also: Chapter 4, "Streams Apply Process" "Configuring a Streams Administrator" Oracle Streams Replication Administrator's Guide for more information about managing DML handlers, DDL handlers, and Streams tags for an apply process

## Creating an Apply Process

You can use any of the following procedures to create an apply process:

- `DBMS_STREAMS_ADM.ADD_TABLE_RULES`

- `DBMS_STREAMS_ADM.ADD_SUBSET_RULES`

- `DBMS_STREAMS_ADM.ADD_SCHEMA_RULES`

- `DBMS_STREAMS_ADM.ADD_GLOBAL_RULES`

- `DBMS_STREAMS_ADM.ADD_MESSAGE_RULE`

- `DBMS_APPLY_ADM.CREATE_APPLY`

Each of the procedures in the `DBMS_STREAMS_ADM` package creates an apply process with the specified name if it does not already exist, creates either a positive rule set or negative rule set for the apply process if the apply process does not have such a rule set, and can add table rules, schema rules, global rules, or a message rule to the rule set.

The `CREATE_APPLY` procedure in the `DBMS_APPLY_ADM` package creates an apply process, but does not create a rule set or rules for the apply process. However, the `CREATE_APPLY` procedure enables you to specify an existing rule set to associate with the apply process, either as a positive or a negative rule set, and a number of other options, such as apply handlers, an apply user, an apply tag, and whether to apply captured messages or user-enqueued messages.

Before you create an apply process, create an `ANYDATA` queue to associate with the apply process, if one does not exist.

Note: Depending on the configuration of the apply process you create, supplemental logging might be required at the source database on columns in the tables for which an apply process applies changes. To create an apply process, a user must be granted `DBA` role.

See Also: "Creating an ANYDATA Queue" "Supplemental Logging in a Streams Environment" for information about supplemental logging "Specifying Supplemental Logging at a Source Database"

### Examples of Creating an Apply Process Using DBMS_STREAMS_ADM

The first example in this section creates an apply process that applies captured messages. The second example in this section creates an apply process that applies user-enqueued messages. A single apply process cannot apply both captured and user-enqueued messages.

- Creating an Apply Process for Captured Messages

- Creating an Apply Process for User-Enqueued Messages

  See Also: "Apply Process Creation" "System-Created Rules" Oracle Streams Replication Administrator's Guide for more information about Streams tags

#### Creating an Apply Process for Captured Messages

The following example runs the `ADD_SCHEMA_RULES` procedure in the `DBMS_STREAMS_ADM` package to create an apply process that applies captured messages:

    BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name        => 'hr',
        streams_type       => 'apply',
        streams_name       => 'strm01_apply',
        queue_name         => 'streams_queue',
        include_dml        => true,
        include_ddl        => false,
        include_tagged_lcr => false,
        source_database    => 'dbs1.net',
        inclusion_rule     => true);
    END;
    /

Running this procedure performs the following actions:

- Creates an apply process named `strm01_apply` that applies captured messages to the local database. The apply process is created only if it does not already exist.

- Associates the apply process with an existing queue named `streams_queue`.

- Creates a positive rule set and associates it with the apply process, if the apply process does not have a positive rule set, because the `inclusion_rule` parameter is set to `true`. The rule set uses the `SYS.STREAMS$_EVALUATION_CONTEXT` evaluation context. The rule set name is system generated.

- Creates one rule that evaluates to `TRUE` for row LCRs that contain the results of DML changes to database objects in the `hr` schema. The rule name is system generated.

- Adds the rule to the positive rule set associated with the apply process because the `inclusion_rule` parameter is set to `true`.

- Sets the `apply_tag` for the apply process to a value that is the hexadecimal equivalent of `'00'` (double zero). Redo entries generated by the apply process have a tag with this value.

- Specifies that the apply process applies a row LCR only if it has a `NULL` tag, because the `include_tagged_lcr` parameter is set to `false`. This behavior is accomplished through the system-created rule for the apply process.

- Specifies that the LCRs applied by the apply process originate at the `dbs1.net` source database. The rules in the apply process rule sets determine which messages are dequeued by the apply process. If the apply process dequeues an LCR with a source database other than `dbs1.net`, then an error is raised.

#### Creating an Apply Process for User-Enqueued Messages

The following example runs the `ADD_MESSAGE_RULE` procedure in the `DBMS_STREAMS_ADM` package to create an apply process:

    BEGIN
      DBMS_STREAMS_ADM.ADD_MESSAGE_RULE(
        message_type       => 'oe.order_typ',
        rule_condition     => ':msg.order_status = 1',
        streams_type       => 'apply',
        streams_name       => 'strm02_apply',
        queue_name         => 'strm02_queue',
        inclusion_rule     => true);
    END;
    /

Running this procedure performs the following actions:

- Creates an apply process named `strm02_apply` that dequeues user-enqueued messages of `oe.order_typ` type and sends them to the message handler for the apply process. The apply process is created only if it does not already exist.

- Associates the apply process with an existing queue named `strm02_queue`.

- Creates a positive rule set and associates it with the apply process, if the apply process does not have a positive rule set, because the `inclusion_rule` parameter is set to `true`. The rule set name is system generated, and the rule set does not use an evaluation context.

- Creates one rule that evaluates to `TRUE` for user-enqueued messages that satisfy the rule condition. The rule uses a system-created evaluation context for the message type. The rule name and the evaluation context name are system generated.

- Adds the rule to the positive rule set associated with the apply process because the `inclusion_rule` parameter is set to `true`.

- Sets the `apply_tag` for the apply process to a value that is the hexadecimal equivalent of `'00'` (double zero). Redo entries generated by the apply process, including any redo entries generated by a message handler, have a tag with this value.

  Note: You can use the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package to specify a message handler for an apply process.

  See Also: "Message Rule Example" "Evaluation Contexts for Message Rules"

### Examples of Creating an Apply Process Using DBMS_APPLY_ADM

The first example in this section creates an apply process that applies captured messages. The second example in this section creates an apply process that applies user-enqueued messages. A single apply process cannot apply both captured and user-enqueued messages.

- Creating an Apply Process for Captured Messages with DBMS_APPLY_ADM

- Creating an Apply Process for User-Enqueued Messages with DBMS_APPLY_ADM

  See Also: "Apply Process Creation" "Message Processing Options for an Apply Process" for more information about apply handlers Oracle Streams Replication Administrator's Guide for more information about Streams tags Oracle Streams Replication Administrator's Guide for information about configuring an apply process to apply messages to a non-Oracle database using the `apply_database_link` parameter

#### Creating an Apply Process for Captured Messages with DBMS_APPLY_ADM

The following example runs the `CREATE_APPLY` procedure in the `DBMS_APPLY_ADM` package to create an apply process that applies captured messages:

    BEGIN
      DBMS_APPLY_ADM.CREATE_APPLY(
        queue_name             => 'strm03_queue',
        apply_name             => 'strm03_apply',
        rule_set_name          => 'strmadmin.strm03_rule_set',
        message_handler        => NULL,
        ddl_handler            => 'strmadmin.history_ddl',
        apply_user             => 'hr',
        apply_database_link    => NULL,
        apply_tag              => HEXTORAW('5'),
        apply_captured         => true,
        precommit_handler      => NULL,
        negative_rule_set_name => NULL,
        source_database        => 'dbs1.net');
    END;
    /

Running this procedure performs the following actions:

- Creates an apply process named `strm03_apply`. An apply process with the same name must not exist.

- Associates the apply process with an existing queue named `strm03_queue`.

- Associates the apply process with an existing rule set named `strm03_rule_set`. This rule set is the positive rule set for the apply process.

- Specifies that the apply process does not use a message handler.

- Specifies that the DDL handler is the `history_ddl` PL/SQL procedure in the `strmadmin` schema. The user who runs the `CREATE_APPLY` procedure must have `EXECUTE` privilege on the `history_ddl` PL/SQL procedure. An example in the Oracle Streams Replication Administrator's Guide creates this procedure.

- Specifies that the user who applies the changes is `hr`, and not the user who is running the `CREATE_APPLY` procedure (the Streams administrator).

- Specifies that the apply process applies changes to the local database because the `apply_database_link` parameter is set to `NULL`.

- Specifies that each redo entry generated by the apply process has a tag that is the hexadecimal equivalent of `'5'`.

- Specifies that the apply process applies captured messages, and not user-enqueued messages. Therefore, if an LCR that was constructed by a user application, not by a capture process, is staged in the queue for the apply process, then this apply process does not apply the LCR.

- Specifies that the apply process does not use a precommit handler.

- Specifies that the apply process does not use a negative rule set.

- Specifies that the LCRs applied by the apply process originate at the `dbs1.net` source database. The rules in the apply process rule sets determine which messages are dequeued by the apply process. If the apply process dequeues an LCR with a source database other than `dbs1.net`, then an error is raised.

#### Creating an Apply Process for User-Enqueued Messages with DBMS_APPLY_ADM

The following example runs the `CREATE_APPLY` procedure in the `DBMS_APPLY_ADM` package to create an apply process that applies user-enqueued messages:

    BEGIN
      DBMS_APPLY_ADM.CREATE_APPLY(
        queue_name             => 'strm04_queue',
        apply_name             => 'strm04_apply',
        rule_set_name          => 'strmadmin.strm04_rule_set',
        message_handler        => 'strmadmin.mes_handler',
        ddl_handler            => NULL,
        apply_user             => NULL,
        apply_database_link    => NULL,
        apply_tag              => NULL,
        apply_captured         => false,
        precommit_handler      => NULL,
        negative_rule_set_name => NULL);
    END;
    /

Running this procedure performs the following actions:

- Creates an apply process named `strm04_apply`. An apply process with the same name must not exist.

- Associates the apply process with an existing queue named `strm04_queue`.

- Associates the apply process with an existing rule set named `strm04_rule_set`. This rule set is the positive rule set for the apply process.

- Specifies that the message handler is the `mes_handler` PL/SQL procedure in the `strmadmin` schema. The user who runs the `CREATE_APPLY` procedure must have `EXECUTE` privilege on the `mes_handler` PL/SQL procedure.

- Specifies that the apply process does not use a DDL handler.

- Specifies that the user who applies the changes is the user who runs the `CREATE_APPLY` procedure, because the `apply_user` parameter is `NULL`.

- Specifies that the apply process applies changes to the local database, because the `apply_database_link` parameter is set to `NULL`.

- Specifies that each redo entry generated by the apply process has a `NULL` tag.

- Specifies that the apply process applies user-enqueued messages, and not captured messages.

- Specifies that the apply process does not use a precommit handler.

- Specifies that the apply process does not use a negative rule set.

## Starting an Apply Process

You run the `START_APPLY` procedure in the `DBMS_APPLY_ADM` package to start an existing apply process. For example, the following procedure starts an apply process named `strm01_apply`:

    BEGIN
      DBMS_APPLY_ADM.START_APPLY(
        apply_name => 'strm01_apply');
    END;
    /

## Stopping an Apply Process

You run the `STOP_APPLY` procedure in the `DBMS_APPLY_ADM` package to stop an existing apply process. For example, the following procedure stops an apply process named `strm01_apply`:

    BEGIN
      DBMS_APPLY_ADM.STOP_APPLY(
        apply_name => 'strm01_apply');
    END;
    /

## Managing the Rule Set for an Apply Process

This section contains instructions for completing the following tasks:

- Specifying the Rule Set for an Apply Process

- Adding Rules to the Rule Set for an Apply Process

- Removing a Rule from the Rule Set for an Apply Process

- Removing a Rule Set for an Apply Process

  See Also: Chapter 5, "Rules" Chapter 6, "How Rules Are Used in Streams"

### Specifying the Rule Set for an Apply Process

You can specify one positive rule set and one negative rule set for an apply process. The apply process applies a message if it evaluates to `TRUE` for at least one rule in the positive rule set and discards a message if it evaluates to `TRUE` for at least one rule in the negative rule set. The negative rule set is evaluated before the positive rule set.

#### Specifying a Positive Rule Set for an Apply Process

You specify an existing rule set as the positive rule set for an existing apply process using the `rule_set_name` parameter in the `ALTER_APPLY` procedure. This procedure is in the `DBMS_APPLY_ADM` package.

For example, the following procedure sets the positive rule set for an apply process named `strm01_apply` to `strm02_rule_set`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name    => 'strm01_apply',
        rule_set_name => 'strmadmin.strm02_rule_set');
    END;
    /

#### Specifying a Negative Rule Set for an Apply Process

You specify an existing rule set as the negative rule set for an existing apply process using the `negative_rule_set_name` parameter in the `ALTER_APPLY` procedure. This procedure is in the `DBMS_APPLY_ADM` package.

For example, the following procedure sets the negative rule set for an apply process named `strm01_apply` to `strm03_rule_set`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name             => 'strm01_apply',
        negative_rule_set_name => 'strmadmin.strm03_rule_set');
    END;
    /

### Adding Rules to the Rule Set for an Apply Process

To add rules to the rule set for an apply process, you can run one of the following procedures:

- `DBMS_STREAMS_ADM.ADD_TABLE_RULES`

- `DBMS_STREAMS_ADM.ADD_SUBSET_RULES`

- `DBMS_STREAMS_ADM.ADD_SCHEMA_RULES`

- `DBMS_STREAMS_ADM.ADD_GLOBAL_RULES`

Excluding the `ADD_SUBSET_RULES` procedure, these procedures can add rules to the positive rule set or negative rule set for an apply process. The `ADD_SUBSET_RULES` procedure can add rules only to the positive rule set for an apply process.

See Also: "System-Created Rules"

#### Adding Rules to the Positive Rule Set for an Apply Process

The following example runs the `ADD_TABLE_RULES` procedure in the `DBMS_STREAMS_ADM` package to add rules to the positive rule set of an apply process named `strm01_apply`:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name       => 'hr.departments',
        streams_type     => 'apply',
        streams_name     => 'strm01_apply',
        queue_name       => 'streams_queue',
        include_dml      => true,
        include_ddl      => true,
        source_database  => 'dbs1.net',
        inclusion_rule   => true);
    END;
    /

Running this procedure performs the following actions:

- Creates one rule that evaluates to `TRUE` for row LCRs that contain the results of DML changes to the `hr.departments` table. The rule name is system generated.

- Creates one rule that evaluates to `TRUE` for DDL LCRs that contain DDL changes to the `hr.departments` table. The rule name is system generated.

- Specifies that both rules evaluate to `TRUE` only for LCRs whose changes originated at the `dbs1.net` source database.

- Adds the rules to the positive rule set associated with the apply process because the `inclusion_rule` parameter is set to `true`.

#### Adding Rules to the Negative Rule Set for an Apply Process

The following example runs the `ADD_TABLE_RULES` procedure in the `DBMS_STREAMS_ADM` package to add rules to the negative rule set of an apply process named `strm01_apply`:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name       => 'hr.regions',
        streams_type     => 'apply',
        streams_name     => 'strm01_apply',
        queue_name       => 'streams_queue',
        include_dml      => true,
        include_ddl      => true,
        source_database  => 'dbs1.net',
        inclusion_rule   => false);
    END;
    /

Running this procedure performs the following actions:

- Creates one rule that evaluates to `TRUE` for row LCRs that contain the results of DML changes to the `hr.regions` table. The rule name is system generated.

- Creates one rule that evaluates to `TRUE` for DDL LCRs that contain DDL changes to the `hr.regions` table. The rule name is system generated.

- Specifies that both rules evaluate to `TRUE` only for LCRs whose changes originated at the `dbs1.net` source database.

- Adds the rules to the negative rule set associated with the apply process because the `inclusion_rule` parameter is set to `false`.

### Removing a Rule from the Rule Set for an Apply Process

You remove a rule from a rule set for an existing apply process by running the `REMOVE_RULE` procedure in the `DBMS_STREAMS_ADM` package. For example, the following procedure removes a rule named `departments3` from the positive rule set of an apply process named `strm01_apply`.

    BEGIN
      DBMS_STREAMS_ADM.REMOVE_RULE(
        rule_name        => 'departments3',
        streams_type     => 'apply',
        streams_name     => 'strm01_apply',
        drop_unused_rule => true,
        inclusion_rule   => true);
    END;
    /

In this example, the `drop_unused_rule` parameter in the `REMOVE_RULE` procedure is set to `true`, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the `drop_unused_rule` parameter is set to `false`, then the rule is removed from the rule set, but it is not dropped from the database even if it is not in any other rule set.

If the `inclusion_rule` parameter is set to `false`, then the `REMOVE_RULE` procedure removes the rule from the negative rule set for the apply process, not from the positive rule set.

To remove all of the rules in a rule set for the apply process, then specify `NULL` for the `rule_name` parameter when you run the `REMOVE_RULE` procedure.

See Also: "Streams Client with One or More Empty Rule Sets"

### Removing a Rule Set for an Apply Process

You remove a rule set from an existing apply process using the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package. This procedure can remove the positive rule set, negative rule set, or both. Specify `true` for the `remove_rule_set` parameter to remove the positive rule set for the apply process. Specify `true` for the `remove_negative_rule_set` parameter to remove the negative rule set for the apply process.

For example, the following procedure removes both the positive and negative rule sets from an apply process named `strm01_apply`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name               => 'strm01_apply',
        remove_rule_set          => true,
        remove_negative_rule_set => true);
    END;
    /

Note: If an apply process that applies captured messages does not have a positive or negative rule set, then the apply process applies all captured messages in its queue. Similarly, if an apply process that applies user-enqueued messages does not have a positive or negative rule set, then the apply process applies all user-enqueued messages in its queue.

## Setting an Apply Process Parameter

Set an apply process parameter using the `SET_PARAMETER` procedure in the `DBMS_APPLY_ADM` package. Apply process parameters control the way an apply process operates.

For example, the following procedure sets the `commit_serialization` parameter for an apply process named `strm01_apply` to `none`. This setting for the `commit_serialization` parameter enables the apply process to commit transactions in any order.

    BEGIN
      DBMS_APPLY_ADM.SET_PARAMETER(
        apply_name   => 'strm01_apply',
        parameter    => 'commit_serialization',
        value        => 'none');
    END;
    /

Note: The `value` parameter is always entered as a `VARCHAR2` value, even if the parameter value is a number. If you set the `parallelism` apply process parameter to a value greater than `1`, then you must specify a conditional supplemental log group at the source database for all of the unique key and foreign key columns in the tables for which an apply process applies changes. supplemental logging might be required for other columns in these tables as well, depending on your configuration.

See Also: "Apply Process Parameters" The `DBMS_APPLY_ADM.SET_PARAMETER` procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the apply process parameters "Specifying Supplemental Logging at a Source Database"

## Setting the Apply User for an Apply Process

The apply user is the user who applies all DML changes and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. Set the apply user for an apply process using the `apply_user` parameter in the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package.

To change the apply user, the user who invokes the `ALTER_APPLY` procedure must be granted `DBA` role. Only the `SYS` user can set the `apply_user` to `SYS`.

For example, the following procedure sets the apply user for an apply process named `strm03_apply` to `hr`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name => 'strm03_apply',
        apply_user => 'hr');
    END;
    /

Running this procedure grants the new apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue. In addition, make sure the apply user has the following privileges:

- `EXECUTE` privilege on the rule sets used by the apply process

- `EXECUTE` privilege on all custom rule-based transformation functions used in the rule set

- `EXECUTE` privilege on all apply handler procedures

These privileges must be granted directly to the apply user. They cannot be granted through roles.

## Managing the Message Handler for an Apply Process

The following sections contain instructions for setting and removing the message handler for an apply process:

- Setting the Message Handler for an Apply Process

- Removing the Message Handler for an Apply Process

See Also: "Message Processing with an Apply Process" Oracle Streams Advanced Queuing User's Guide and Reference for an example that creates a message handler

### Setting the Message Handler for an Apply Process

Set the message handler for an apply process using the `message_handler` parameter in the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package. For example, the following procedure sets the message handler for an apply process named `strm03_apply` to the `mes_handler` procedure in the `oe` schema.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name      => 'strm03_apply',
        message_handler => 'oe.mes_handler');
    END;
    /

The user who runs the `ALTER_APPLY` procedure must have `EXECUTE` privilege on the specified message handler.

### Removing the Message Handler for an Apply Process

You remove the message handler for an apply process by setting the `remove_message_handler` parameter to `true` in the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package. For example, the following procedure removes the message handler from an apply process named `strm03_apply`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name             => 'strm03_apply',
        remove_message_handler => true);
    END;
    /

## Managing the Precommit Handler for an Apply Process

The following sections contain instructions for creating, setting, and removing the precommit handler for an apply process:

- Creating a Precommit Handler for an Apply Process

- Setting the Precommit Handler for an Apply Process

- Removing the Precommit Handler for an Apply Process

### Creating a Precommit Handler for an Apply Process

A precommit handler must have the following signature:

    PROCEDURE handler_procedure (
       parameter_name   IN  NUMBER);

Here, `handler_procedure` stands for the name of the procedure and `parameter_name` stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is a commit SCN from an internal commit directive in the queue used by the apply process.

You can use a precommit handler to record information about commits processed by an apply process. The apply process can apply captured messages or user-enqueued messages. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database. For a user-enqueued message, the commit SCN is generated by the apply process.

The precommit handler procedure must conform to the following restrictions:

- Any work that commits must be an autonomous transaction.

- Any rollback must be to a named savepoint created in the procedure.

If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the messages in the transaction are moved to the error queue.

For example, a precommit handler can be used for auditing the row LCRs applied by an apply process. Such a precommit handler is used with one or more separate DML handlers to record the source database commit SCN for a transaction, and possibly the time when the apply process applies the transaction, in an audit table.

Specifically, this example creates a precommit handler that is used with a DML handler that records information about row LCRs in the following table:

    CREATE TABLE strmadmin.history_row_lcrs(
      timestamp             DATE,
      source_database_name  VARCHAR2(128),
      command_type          VARCHAR2(30),
      object_owner          VARCHAR2(32),
      object_name           VARCHAR2(32),
      tag                   RAW(10),
      transaction_id        VARCHAR2(10),
      scn                   NUMBER,
      commit_scn            NUMBER,
      old_values            SYS.LCR$_ROW_LIST,
      new_values            SYS.LCR$_ROW_LIST)
        NESTED TABLE old_values STORE AS old_values_ntab
        NESTED TABLE new_values STORE AS new_values_ntab;

The DML handler inserts a row in the `strmadmin.history_row_lcrs` table for each row LCR processed by an apply process. The precommit handler created in this example inserts a row into the `strmadmin.history_row_lcrs` table when a transaction commits.

Create the procedure that inserts the commit information into the `history_row_lcrs` table:

    CREATE OR REPLACE PROCEDURE strmadmin.history_commit(commit_number IN NUMBER)
     IS
     BEGIN
      -- Insert commit information into the history_row_lcrs table
      INSERT INTO strmadmin.history_row_lcrs (timestamp, commit_scn)
        VALUES (SYSDATE, commit_number);
    END;
    /

See Also: "Audit Commit Information for Messages Using Precommit Handlers" Oracle Streams Replication Administrator's Guide for more information about the DML handler referenced in this example

### Setting the Precommit Handler for an Apply Process

A precommit handler processes all commit directives dequeued by an apply process.

Set the precommit handler for an apply process using the `precommit_handler` parameter in the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package. For example, the following procedure sets the precommit handler for an apply process named `strm01_apply` to the `history_commit` procedure in the `strmadmin` schema.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name        => 'strm01_apply',
        precommit_handler => 'strmadmin.history_commit');
    END;
    /

You can also specify a precommit handler when you create an apply process using the `CREATE_APPLY` procedure in the `DBMS_APPLY_ADM` package.

### Removing the Precommit Handler for an Apply Process

You remove the precommit handler for an apply process by setting the `remove_precommit_handler` parameter to `true` in the `ALTER_APPLY` procedure in the `DBMS_APPLY_ADM` package. For example, the following procedure removes the precommit handler from an apply process named `strm01_apply`.

    BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
        apply_name               => 'strm01_apply',
        remove_precommit_handler => true);
    END;
    /

## Specifying Message Enqueues by Apply Processes

This section contains instructions for setting a destination queue into which apply processes that use a specified rule in a positive rule set will enqueue messages that satisfy the rule. This section also contains instructions for removing destination queue settings.

See Also: "Viewing Rules that Specify a Destination Queue on Apply"

### Setting the Destination Queue for Messages that Satisfy a Rule

You use the `SET_ENQUEUE_DESTINATION` procedure in the `DBMS_APPLY_ADM` package to set a destination queue for messages that satisfy a specific rule. For example, to set the destination queue for a rule named `employees5` to the queue `hr.change_queue`, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
        rule_name               =>  'employees5',
        destination_queue_name  =>  'hr.change_queue');
    END;
    /

This procedure modifies the action context of the rule to specify the queue. Any apply process in the local database with the `employees5` rule in its positive rule set will enqueue a message into `hr.change_queue` if the message satisfies the `employees5` rule. If you want to change the destination queue for the `employees5` rule, then run the `SET_ENQUEUE_DESTINATION` procedure again and specify a different queue.

The apply user of each apply process using the specified rule must have the necessary privileges to enqueue messages into the specified queue. If the queue is a secure queue, then the apply user must be a secure queue user of the queue.

A message that has been enqueued into an queue using the `SET_ENQUEUE_DESTINATION` procedure is the same as any other user-enqueued message. Such messages can be manually dequeued, applied by an apply process created with the `apply_captured` parameter set to `false`, or propagated to another queue.

Note: The specified rule must be in the positive rule set for an apply process. If the rule is in the negative rule set for an apply process, then the apply process does not enqueue the message into the destination queue.

See Also: "Enabling a User to Perform Operations on a Secure Queue" "Enqueue Destinations for Messages During Apply" for more information about how the `SET_ENQUEUE_DESTINATION` procedure modifies the action context of the specified rule

### Removing the Destination Queue Setting for a Rule

You use the `SET_ENQUEUE_DESTINATION` procedure in the `DBMS_APPLY_ADM` package to remove a destination queue for messages that satisfy a specified rule. Specifically, you set the `destination_queue_name` parameter in this procedure to `NULL` for the rule. When a destination queue specification is removed for a rule, messages that satisfy the rule are no longer enqueued into the queue by an apply process.

For example, to remove the destination queue for a rule named `employees5`, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
        rule_name               =>  'employees5',
        destination_queue_name  =>  NULL);
    END;
    /

Any apply process in the local database with the `employees5` rule in its positive rule set no longer enqueues a message into `hr.change_queue` if the message satisfies the `employees5` rule.

## Specifying Execute Directives for Apply Processes

This section contains instructions for setting an apply process execute directive for messages that satisfy a specified rule in the positive rule set for the apply process.

See Also: "Viewing Rules that Specify No Execution on Apply"

### Specifying that Messages that Satisfy a Rule Are Not Executed

You use the `SET_EXECUTE` procedure in the `DBMS_APPLY_ADM` package to specify that apply processes do not execute messages that satisfy a specified rule. Specifically, you set the `execute` parameter in this procedure to `false` for the rule. After setting the execution directive to `false` for a rule, an apply process with the rule in its positive rule set does not execute a message that satisfies the rule.

For example, to specify that apply processes do not execute messages that satisfy a rule named `departments8`, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_EXECUTE(
        rule_name   =>  'departments8',
        execute     =>  false);
    END;
    /

This procedure modifies the action context of the rule to specify the execution directive. Any apply process in the local database with the `departments8` rule in its positive rule set will not execute a message if the message satisfies the `departments8` rule. That is, if the message is an LCR, then an apply process does not apply the change in the LCR to the relevant database object. Also, an apply process does not send a message that satisfies this rule to any apply handler.

Note: The specified rule must be in the positive rule set for an apply process for the apply process to follow the execution directive. If the rule is in the negative rule set for an apply process, then the apply process ignores the execution directive for the rule. The `SET_EXECUTE` procedure can be used with the `SET_ENQUEUE_DESTINATION` procedure if you want to enqueue messages that satisfy a particular rule into a destination queue without executing these messages. After a message is enqueued using the `SET_ENQUEUE_DESTINATION` procedure, it is a user-enqueued message in the destination queue. Therefore, it can be manually dequeued, applied by an apply process, or propagated to another queue.

See Also: "Execution Directives for Messages During Apply" for more information about how the `SET_EXECUTE` procedure modifies the action context of the specified rule "Specifying Message Enqueues by Apply Processes"

### Specifying that Messages that Satisfy a Rule Are Executed

You use the `SET_EXECUTE` procedure in the `DBMS_APPLY_ADM` package to specify that apply processes execute messages that satisfy a specified rule. Specifically, you set the `execute` parameter in this procedure to `true` for the rule. By default, each apply process executes messages that satisfy a rule in the positive rule set for the apply process, assuming that the message does not satisfy a rule in the negative rule set for the apply process. Therefore, you need to set the `execute` parameter to `true` for a rule only if this parameter was set to `false` for the rule in the past.

For example, to specify that apply processes executes messages that satisfy a rule named `departments8`, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.SET_EXECUTE(
        rule_name   =>  'departments8',
        execute     =>  true);
    END;
    /

Any apply process in the local database with the `departments8` rule in its positive rule set will execute a message if the message satisfies the `departments8` rule. That is, if the message is an LCR, then an apply process applies the change in the LCR to the relevant database object. Also, an apply process sends a message that satisfies this rule to an apply handler if it is configured to do so.

## Managing an Error Handler

The following sections contain instructions for creating, setting, and removing an error handler:

- Creating an Error Handler

- Setting an Error Handler

- Unsetting an Error Handler

See Also: "Message Processing with an Apply Process"

### Creating an Error Handler

You create an error handler by running the `SET_DML_HANDLER` procedure in the `DBMS_APPLY_ADM` package and setting the `error_handler` parameter to `true`.

An error handler must have the following signature:

    PROCEDURE user_procedure (
         message             IN ANYDATA,
         error_stack_depth   IN NUMBER,
         error_numbers       IN DBMS_UTILITY.NUMBER_ARRAY,
         error_messages      IN emsg_array);

Here, `user_procedure` stands for the name of the procedure. Each parameter is required and must have the specified datatype. However, you can change the names of the parameters. The `emsg_array` parameter must be a user-defined array that is a PL/SQL table of type `VARCHAR2` with at least 76 characters.

Note: Some conditions on the user procedure specified in `SET_DML_HANDLER` must be met for error handlers. See Oracle Streams Replication Administrator's Guide for information about these conditions.

Running an error handler results in one of the following outcomes:

- The error handler successfully resolves the error, applies the row LCR if appropriate, and returns control back to the apply process.

- The error handler fails to resolve the error, and the error is raised. The raised error causes the transaction to be rolled back and placed in the error queue.

If you want to retry the DML operation, then have the error handler procedure run the `EXECUTE` member procedure for the LCR.

The following example creates an error handler named `regions_pk_error` that resolves primary key violations for the `hr.regions` table. At a destination database, assume users insert rows into the `hr.regions` table and an apply process applies changes to the `hr.regions` table that originated from a capture process at a remote source database. In this environment, there is a possibility of errors resulting from users at the destination database inserting a row with the same primary key value as an insert row LCR applied from the source database.

This example creates a table in the `strmadmin` schema called `errorlog` to record the following information about each primary key violation error on the `hr.regions` table:

- The timestamp when the error occurred

- The name of the apply process that raised the error

- The user who caused the error (sender), which is the capture process name for captured messages or the name of the AQ agent for user-enqueued LCRs

- The name of the object on which the DML operation was run, because errors for other objects might be logged in the future

- The type of command used in the DML operation

- The name of the constraint violated

- The error message

- The LCR that caused the error

This error handler resolves only errors that are caused by a primary key violation on the `hr.regions` table. To resolve this type of error, the error handler modifies the `region_id` value in the row LCR using a sequence and then executes the row LCR to apply it. If other types of errors occur, then you can use the row LCR you stored in the `errorlog` table to resolve the error manually.

For example, the following error is resolved by the error handler:

1. At the destination database, a user inserts a row into the `hr.regions` table with a `region_id` value of `6` and a `region_name` value of `'LILLIPUT'`.

2. At the source database, a user inserts a row into the `hr.regions` table with a `region_id` value of `6` and a `region_name` value of `'BROBDINGNAG'`.

3. A capture process at the source database captures the change described in Step 2.

4. A propagation propagates the LCR containing the change from a queue at the source database to the queue used by the apply process at the destination database.

5. When the apply process tries to apply the LCR, an error results because of a primary key violation.

6. The apply process invokes the error handler to handle the error.

7. The error handler logs the error in the `strmadmin.errorlog` table.

8. The error handler modifies the `region_id` value in the LCR using a sequence and executes the LCR to apply it.

Complete the following steps to create the `regions_pk_error` error handler:

1. Create the sequence used by the error handler to assign new primary key values by connecting as `hr` user and running the following statement:

       CONNECT hr/hr

       CREATE SEQUENCE hr.reg_exception_s START WITH 9000;

   This example assumes that users at the destination database will never insert a row into the `hr.regions` table with a `region_id` greater than `8999`.

2. Grant the Streams administrator `ALL` privilege on the sequence:

       GRANT ALL ON reg_exception_s TO strmadmin;

3. Create the `errorlog` table by connecting as the Streams administrator and running the following statement:

       CONNECT strmadmin/strmadminpw

       CREATE TABLE strmadmin.errorlog(
         logdate       DATE,
         apply_name    VARCHAR2(30),
         sender        VARCHAR2(100),
         object_name   VARCHAR2(32),
         command_type  VARCHAR2(30),
         errnum        NUMBER,
         errmsg        VARCHAR2(2000),
         text          VARCHAR2(2000),
         lcr           SYS.LCR$_ROW_RECORD);

4. Create a package that includes the `regions_pk_error` procedure:

       CREATE OR REPLACE PACKAGE errors_pkg
       AS
        TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
        PROCEDURE regions_pk_error(
          message            IN ANYDATA,
          error_stack_depth  IN NUMBER,
          error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
          error_messages     IN EMSG_ARRAY);
       END errors_pkg ;
       /

5. Create the package body:

       CREATE OR REPLACE PACKAGE BODY errors_pkg AS
        PROCEDURE regions_pk_error (
          message            IN ANYDATA,
          error_stack_depth  IN NUMBER,
          error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
          error_messages     IN EMSG_ARRAY )
        IS
         reg_id     NUMBER;
         ad         ANYDATA;
         lcr        SYS.LCR$_ROW_RECORD;
         ret        PLS_INTEGER;
         vc         VARCHAR2(30);
         apply_name VARCHAR2(30);
         errlog_rec errorlog%ROWTYPE ;
         ov2        SYS.LCR$_ROW_LIST;
        BEGIN
         -- Access the error number from the top of the stack.
         -- In case of check constraint violation,
         -- get the name of the constraint violated.
         IF error_numbers(1) IN ( 1 , 2290 ) THEN
          ad  := DBMS_STREAMS.GET_INFORMATION('CONSTRAINT_NAME');
          ret := ad.GetVarchar2(errlog_rec.text);
         ELSE
          errlog_rec.text := NULL ;
         END IF ;
         -- Get the name of the sender and the name of the apply process.
         ad  := DBMS_STREAMS.GET_INFORMATION('SENDER');
         ret := ad.GETVARCHAR2(errlog_rec.sender);
         apply_name := DBMS_STREAMS.GET_STREAMS_NAME();
         -- Try to access the LCR.
         ret := message.GETOBJECT(lcr);
         errlog_rec.object_name  := lcr.GET_OBJECT_NAME() ;
         errlog_rec.command_type := lcr.GET_COMMAND_TYPE() ;
         errlog_rec.errnum := error_numbers(1) ;
         errlog_rec.errmsg := error_messages(1) ;
         INSERT INTO strmadmin.errorlog VALUES (SYSDATE, apply_name,
              errlog_rec.sender, errlog_rec.object_name, errlog_rec.command_type,
              errlog_rec.errnum, errlog_rec.errmsg, errlog_rec.text, lcr);
         -- Add the logic to change the contents of LCR with correct values.
         -- In this example, get a new region_id number
         -- from the hr.reg_exception_s sequence.
         ov2 := lcr.GET_VALUES('new', 'n');
         FOR i IN 1 .. ov2.count
         LOOP
           IF ov2(i).column_name = 'REGION_ID' THEN
            SELECT hr.reg_exception_s.NEXTVAL INTO reg_id FROM DUAL;
            ov2(i).data := ANYDATA.ConvertNumber(reg_id) ;
           END IF ;
         END LOOP ;
         -- Set the NEW values in the LCR.
         lcr.SET_VALUES(value_type => 'NEW', value_list => ov2);
         -- Execute the modified LCR to apply it.
         lcr.EXECUTE(true);
        END regions_pk_error;
       END errors_pkg;
       /

   Note: For subsequent changes to the modified row to be applied successfully, you should converge the rows at the two databases as quickly as possible. That is, you should make the `region_id` for the row match at the source and destination database. If you do not want these manual changes to be recaptured at a database, then use the `SET_TAG` procedure in the `DBMS_STREAMS` package to set the tag for the session in which you make the change to a value that is not captured. This example error handler illustrates the use of the `GET_VALUES` member function and `SET_VALUES` member procedure for the LCR. If you are modifying only one value in the LCR, then the `GET_VALUE` member function and `SET_VALUE` member procedure might be more convenient and more efficient.

   See Also: Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session

### Setting an Error Handler

An error handler handles errors resulting from a row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple error handlers on the same table, to handle errors resulting from different operations on the table. You can either set an error handler for a specific apply process, or you can set an error handler as a general error handler that is used by all apply processes that apply the specified operation to the specified table.

Set an error handler using the `SET_DML_HANDLER` procedure in the `DBMS_APPLY_ADM` package. When you run this procedure to set an error handler, set the `error_handler` parameter to `true`.

For example, the following procedure sets the error handler for `INSERT` operations on the `hr.regions` table. Therefore, when any apply process dequeues a row LCR containing an `INSERT` operation on the local `hr.regions` table, and the row LCR results in an error, the apply process sends the row LCR to the `strmadmin.errors_pkg.regions_pk_error` PL/SQL procedure for processing. If the error handler cannot resolve the error, then the row LCR and all of the other row LCRs in the same transaction are moved to the error queue.

In this example, the `apply_name` parameter is set to `NULL`. Therefore, the error handler is a general error handler that is used by all of the apply processes in the database.

Run the following procedure to set the error handler:

    BEGIN
      DBMS_APPLY_ADM.SET_DML_HANDLER(
        object_name         => 'hr.regions',
        object_type         => 'TABLE',
        operation_name      => 'INSERT',
        error_handler       => true,
        user_procedure      => 'strmadmin.errors_pkg.regions_pk_error',
        apply_database_link => NULL,
        apply_name          => NULL);
    END;
    /

### Unsetting an Error Handler

You unset an error handler using the `SET_DML_HANDLER` procedure in the `DBMS_APPLY_ADM` package. When you run that procedure, set the `user_procedure` parameter to `NULL` for a specific operation on a specific table.

For example, the following procedure unsets the error handler for `INSERT` operations on the `hr.regions` table:

    BEGIN
      DBMS_APPLY_ADM.SET_DML_HANDLER(
        object_name    => 'hr.regions',
        object_type    => 'TABLE',
        operation_name => 'INSERT',
        user_procedure => NULL,
        apply_name     => NULL);
    END;
    /

Note: The `error_handler` parameter does not need to be specified.

## Managing Apply Errors

The following sections contain instructions for retrying and deleting apply errors:

- Retrying Apply Error Transactions

- Deleting Apply Error Transactions

See Also: "The Error Queue" "Checking for Apply Errors" "Displaying Detailed Information About Apply Errors" Oracle Streams Replication Administrator's Guide for information about the possible causes of apply errors

### Retrying Apply Error Transactions

You can retry a specific error transaction or you can retry all error transactions for an apply process. You might need to make DML or DDL changes to database objects to correct the conditions that caused one or more apply errors before you retry error transactions. You can also have one or more capture processes configured to capture changes to the same database objects, but you might not want the changes captured. In this case, you can set the session tag to a value that will not be captured for the session that makes the changes.

See Also: Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session

#### Retrying a Specific Apply Error Transaction

When you retry an error transaction, you can execute it immediately or send the error transaction to a user procedure for modifications before executing it. The following sections provide instructions for each method:

- Retrying a Specific Apply Error Transaction Without a User Procedure

- Retrying a Specific Apply Error Transaction with a User Procedure

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about the `EXECUTE_ERROR` procedure

##### Retrying a Specific Apply Error Transaction Without a User Procedure

After you correct the conditions that caused an apply error, you can retry the transaction by running the `EXECUTE_ERROR` procedure in the `DBMS_APPLY_ADM` package without specifying a user procedure. In this case, the transaction is executed without any custom processing.

For example, to retry a transaction with the transaction identifier `5.4.312`, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.EXECUTE_ERROR(
        local_transaction_id => '5.4.312',
        execute_as_user      => false,
        user_procedure       => NULL);
    END;
    /

If `execute_as_user` is `true`, then the apply process executes the transaction in the security context of the current user. If `execute_as_user` is `false`, then the apply process executes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised.

In either case, the user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

##### Retrying a Specific Apply Error Transaction with a User Procedure

You can retry an error transaction by running the `EXECUTE_ERROR` procedure in the `DBMS_APPLY_ADM` package, and specify a user procedure to modify one or more messages in the transaction before the transaction is executed. The modifications should enable successful execution of the transaction. The messages in the transaction can be LCRs or user messages.

For example, consider a case in which an apply error resulted because of a conflict. Examination of the error transaction reveals that the old value for the `salary` column in a row LCR contained the wrong value. Specifically, the current value of the salary of the employee with `employee_id` of `197` in the `hr.employees` table did not match the old value of the salary for this employee in the row LCR. Assume that the current value for this employee is `3250` in the `hr.employees` table.

Given this scenario, the following user procedure modifies the salary in the row LCR that caused the error:

    CREATE OR REPLACE PROCEDURE strmadmin.modify_emp_salary(
      in_any                        IN      ANYDATA,
      error_record                  IN      DBA_APPLY_ERROR%ROWTYPE,
      error_message_number          IN      NUMBER,
      messaging_default_processing  IN OUT  BOOLEAN,
      out_any                       OUT     ANYDATA)
    AS
      row_lcr          SYS.LCR$_ROW_RECORD;
      row_lcr_changed  BOOLEAN := FALSE;
      res              NUMBER;
      ob_owner         VARCHAR2(32);
      ob_name          VARCHAR2(32);
      cmd_type         VARCHAR2(30);
      employee_id      NUMBER;
    BEGIN
      IF in_any.getTypeName() = 'SYS.LCR$_ROW_RECORD' THEN
        -- Access the LCR
        res := in_any.GETOBJECT(row_lcr);
        -- Determine the owner of the database object for the LCR
        ob_owner := row_lcr.GET_OBJECT_OWNER;
        -- Determine the name of the database object for the LCR
        ob_name := row_lcr.GET_OBJECT_NAME;
        -- Determine the type of DML change
        cmd_type := row_lcr.GET_COMMAND_TYPE;
        IF (ob_owner = 'HR' AND ob_name = 'EMPLOYEES' AND cmd_type = 'UPDATE') THEN
          -- Determine the employee_id of the row change
          IF row_lcr.GET_VALUE('old', 'employee_id') IS NOT NULL THEN
            employee_id := row_lcr.GET_VALUE('old', 'employee_id').ACCESSNUMBER();
            IF (employee_id = 197) THEN
              -- error_record.message_number should equal error_message_number
              row_lcr.SET_VALUE(
              value_type => 'OLD',
              column_name => 'salary',
              column_value => ANYDATA.ConvertNumber(3250));
              row_lcr_changed := TRUE;
            END IF;
          END IF;
        END IF;
      END IF;
      -- Specify that the apply process continues to process the current message
      messaging_default_processing := TRUE;
      -- assign out_any appropriately
      IF row_lcr_changed THEN
        out_any := ANYDATA.ConvertObject(row_lcr);
      ELSE
        out_any := in_any;
      END IF;
    END;
    /

To retry a transaction with the transaction identifier `5.6.924` and process the transaction with the `modify_emp_salary` procedure in the `strmadmin` schema before execution, run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.EXECUTE_ERROR(
        local_transaction_id => '5.6.924',
        execute_as_user      => false,
        user_procedure       => 'strmadmin.modify_emp_salary');
    END;
    /

Note: The user who runs the procedure must have `SELECT` privilege on `DBA_APPLY_ERROR` data dictionary view.

See Also: "Displaying Detailed Information About Apply Errors"

#### Retrying All Error Transactions for an Apply Process

After you correct the conditions that caused all of the apply errors for an apply process, you can retry all of the error transactions by running the `EXECUTE_ALL_ERRORS` procedure in the `DBMS_APPLY_ADM` package. For example, to retry all of the error transactions for an apply process named `strm01_apply`, you can run the following procedure:

    BEGIN
      DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
        apply_name       => 'strm01_apply',
        execute_as_user  => false);
    END;
    /

Note: If you specify `NULL` for the `apply_name` parameter, and you have multiple apply processes, then all of the apply errors are retried for all of the apply processes.

### Deleting Apply Error Transactions

You can delete a specific error transaction or you can delete all error transactions for an apply process.

#### Deleting a Specific Apply Error Transaction

If an error transaction should not be applied, then you can delete the transaction from the error queue using the `DELETE_ERROR` procedure in the `DBMS_APPLY_ADM` package. For example, to delete a transaction with the transaction identifier `5.4.312`, run the following procedure:

    EXEC DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id => '5.4.312');

#### Deleting All Error Transactions for an Apply Process

If none of the error transactions should be applied, then you can delete all of the error transactions by running the `DELETE_ALL_ERRORS` procedure in the `DBMS_APPLY_ADM` package. For example, to delete all of the error transactions for an apply process named `strm01_apply`, you can run the following procedure:

    EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'strm01_apply');

Note: If you specify `NULL` for the `apply_name` parameter, and you have multiple apply processes, then all of the apply errors are deleted for all of the apply processes.

## Dropping an Apply Process

You run the `DROP_APPLY` procedure in the `DBMS_APPLY_ADM` package to drop an existing apply process. For example, the following procedure drops an apply process named `strm02_apply`:

    BEGIN
      DBMS_APPLY_ADM.DROP_APPLY(
        apply_name            => 'strm02_apply',
        drop_unused_rule_sets => true);
    END;
    /

Because the `drop_unused_rule_sets` parameter is set to `true`, this procedure also drops any rule sets used by the `strm02_apply` apply process, unless a rule set is used by another Streams client. If the `drop_unused_rule_sets` parameter is set to `true`, then both the positive and negative rule set for the apply process might be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.

An error is raised if you try to drop an apply process and there are errors in the error queue for the specified apply process. Therefore, if there are errors in the error queue for an apply process, delete the errors before dropping the apply process.

See Also: "Managing Apply Errors"